package org.test.entrasalida.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.test.comun.Constantes;

public abstract class AbstractExcel {

	protected FileInputStream fileInputStream;
	protected POIFSFileSystem fsFileSystem;
	protected HSSFWorkbook workBook;
	protected ByteArrayOutputStream out;
	protected ByteArrayInputStream xls;
	protected List<Short> nbColumnBySheet = new ArrayList<Short>();
	protected List<HSSFSheet> sheets = new ArrayList<HSSFSheet>();

	public final Integer getColumaIdx(String columLetra) {
		Integer idx = 0;
		for (int i= 0; i < Constantes.A_TO_Z.length ; i++) {
			if (String.valueOf(Constantes.A_TO_Z[i]).equalsIgnoreCase(columLetra)) {
				idx = i;
				break;
			}
		}
		return idx;
	}
	
	public String getColumLetter(int colIdx) {
		String colLetter = "";
		int maxPosByLetter = Constantes.A_TO_Z.length - 1;
		
		int loops = 1;
		
		if (colIdx != maxPosByLetter) {
			loops += (colIdx / maxPosByLetter);
		}
		
		for (int i = 1; i <= loops ; i++) {
			int auxPosIdx = colIdx;
			if (maxPosByLetter < auxPosIdx) {
				if (i < loops) {
					auxPosIdx = loops - 2 ;
				} else {
					auxPosIdx = auxPosIdx - (maxPosByLetter * (loops -1)) - 1;
				}
			}
			
			if (auxPosIdx > -1) {
				colLetter += Constantes.A_TO_Z[auxPosIdx]; 
			}
		}
		
		return colLetter.toUpperCase();
	}
	
	public HSSFWorkbook openExcelFile(String fileName) throws FileNotFoundException,
	IOException {
		fileInputStream = new FileInputStream(fileName);
		fsFileSystem = new POIFSFileSystem(fileInputStream);
		return new HSSFWorkbook(fsFileSystem);
	}	

	public void applySheetStyle(int SheetIdx, int[] reportSizes) throws Exception {
		HSSFSheet sheet = sheets.get(SheetIdx);

		for (int i = 0; i < reportSizes.length; i++) {
			sheet.setColumnWidth(i, reportSizes[i]);
			sheet.setDefaultColumnStyle(i, ReportPOIUtils.getDefaultFormat());
		}
		for (int i = reportSizes.length; i < nbColumnBySheet.get(0) + 2; i++) {
			sheet.setColumnWidth(i, ReportPOIUtils.REPORT_DEFAULT_SIZE);
			sheet.setDefaultColumnStyle(i, ReportPOIUtils.getDefaultFormat());
		}
		setAdditionalStyles();
	}
	
	protected abstract void setAdditionalStyles();

	public void saveExcel() throws Exception {
		try {
			workBook.write(out);
			out.close();
		} catch (IOException e) {
			throw new Exception("Error at writing and closing excel file.");
		}
	}	

	public HSSFSheet getSheet(String sheetName) {
		HSSFSheet sheetRslt = null;
		if (sheets != null && !sheets.isEmpty()) {
			if (sheets.size() == 1) {
				sheetRslt = sheets.get(0); 
			} else {
				for (HSSFSheet sheet: sheets) {
					if (sheet.getSheetName().equalsIgnoreCase(sheetName)) {
						sheetRslt = sheet;
						break;
					}
				}
			}
		}
		return sheetRslt;
	}
	
	public HSSFSheet createSheet(String sheetName) throws Exception {
		if (workBook == null) {
			out = new ByteArrayOutputStream();
			workBook = new HSSFWorkbook();
			ReportPOIUtils.initFormat(workBook);
		}
		
		HSSFSheet sheet = workBook.createSheet(sheetName);
		sheets.add(sheet);
		return sheet;
	}

	protected void updateNbColum(int sheetIdx, HSSFRow row) {
		Short nbColumn = 0;
		if (!nbColumnBySheet.isEmpty() && nbColumnBySheet.size() > sheetIdx) {
			nbColumn = nbColumnBySheet.get(sheetIdx);
		}
		nbColumn = row.getLastCellNum();
		nbColumnBySheet.add(sheetIdx, nbColumn);
	}

	protected void addCell(int col, HSSFRow row, String text,
			HSSFCellStyle format) {

		HSSFCell cell = row.createCell(col);
		cell.setCellStyle(format);
		cell.setCellValue(new HSSFRichTextString(text));
	}

	protected void addCell(int col, HSSFRow row, int count, HSSFCellStyle format) {
		if (count > 0) {
			HSSFCell cell = row.createCell(col);
			cell.setCellStyle(format);
			cell.setCellValue(count);
		} else {
			addCell(col, row, "", format);
		}
	}

	protected void addCell(int col, HSSFRow row, Date date, HSSFCellStyle format) {
		HSSFCell cell = row.createCell(col);
		cell.setCellStyle(format);
		cell.setCellValue(date);
	}

	public List<List<HSSFCell>> readExcelFileSimpleSheet(String fileName, String sheetName) {
		List<List<HSSFCell>> cellDataList = new ArrayList<List<HSSFCell>>();

		try {
			workBook = openExcelFile(fileName);

			int sheetIdx = 0;
			if (StringUtils.isNotBlank(sheetName)) {
				sheetIdx = workBook.getSheetIndex(sheetName);
				if (sheetIdx < 0) {
					throw new Exception("Error abriendo Hoja de excel.");
				}
			}

			HSSFSheet hssfSheet = workBook.getSheetAt(sheetIdx);

			Iterator<Row> rowIterator = hssfSheet.rowIterator();
			while (rowIterator.hasNext())
			{
				HSSFRow hssfRow = (HSSFRow) rowIterator.next();
				Iterator<Cell> iterator = (Iterator<Cell>) hssfRow.cellIterator();
				List<HSSFCell> cellTempList = new ArrayList<HSSFCell>();
				while (iterator.hasNext())
				{
					HSSFCell hssfCell = (HSSFCell) iterator.next();
					cellTempList.add(hssfCell);
				}
				cellDataList.add(cellTempList);
			}
		} catch (Exception e) {
			//TODO cambiar log de este error
			e.printStackTrace();
		}

		return cellDataList;
	}
	
	public HSSFSheet readExcelFileSheet(String fileName, String sheetName) {
		try {
			workBook = openExcelFile(fileName);

			int sheetIdx = 0;
			if (StringUtils.isNotBlank(sheetName)) {
				sheetIdx = workBook.getSheetIndex(sheetName);
				if (sheetIdx < 0) {
					throw new Exception("Error abriendo Hoja de excel.");
				}
			}

			HSSFSheet hssfSheet = workBook.getSheetAt(sheetIdx);
			return hssfSheet;

		} catch (Exception e) {
			//TODO cambiar log de este error
			e.printStackTrace();
		}
		
		return null;
	}	

	public void printToConsole(List<List<HSSFCell>> cellDataList) {
		for (int i = 0; i < cellDataList.size(); i++)
		{
			List<HSSFCell> cellTempList = cellDataList.get(i);
			for (int j = 0; j < cellTempList.size(); j++)
			{
				HSSFCell hssfCell = (HSSFCell) cellTempList.get(j);
				String stringCellValue = hssfCell.toString();
				System.out.print(stringCellValue + "\t");
			}
			//TODO sacar System.out
			System.out.println();
		}
	}


}

